<?php
	include_once $config["DB_DRIVER"].".php";
	
	$db_error_msg = null;
	
	function get_sql_for_object($obj, $single_record=false, $specific_group=true){
		$sql = "";
		$sql_single = "";
		$alias_list = array();
		
		//TODO: EXCEPTION - WHEN THE TABLE IS GROUPS, WE SHOULD ONLY LOAD 
		//		GROUPS WHICH THIS USER OWNS.
		
		/* output in necessary format */
		if (property_exists($obj, "fields")){
			$sql_fields = "";
			$sql_tables = "";
			$table_alias = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");
			$link_cnt = 1;
			$main_table = "A";
			$alias_list[] = array("alias"=>$main_table, "table"=>$obj->sql_table);
			foreach($obj->fields as $field){
				if (property_exists($field, "foreign_key")){
					$tbl = $table_alias[$link_cnt];
					
					$alias_list[] = array("alias"=>$tbl, "table"=>$field->foreign_key->sql_table);
					
					$sql_tables .= " LEFT JOIN ".$field->foreign_key->sql_table. " $tbl ON $tbl.".$field->foreign_key->key." = $main_table.". $field->name;
					
					//if (!property_exists($field, "lookup_only") || (property_exists($field, "lookup_only") && $field->lookup_only == false)){
						if ($sql_fields != "") $sql_fields .= ",";
						
						$sql_fields .= " $tbl.". $field->foreign_key->value; //." AS ". $field->name;
						if (!property_exists($field, "alias")){
							$sql_fields .= " AS ".$field->name;
						} else {
							$sql_fields .= " AS ".$field->alias;
						}
						
					//}
					$link_cnt++;
				}
				else{
					if ($sql_fields != "") $sql_fields .= ",";
					
					$sql_fields .= " $main_table.". $field->name;

					if (property_exists($field, "alias")){
						$sql_fields .= " AS ".$field->alias;
					}

				}
			}
			
			if (true == $single_record){
				foreach($obj->fields as $field){
					if (property_exists($field, "primary_key") && $field->primary_key == true){
						foreach($obj as $key => $value){
							if ($key == $field->name)
								$sql_single = $main_table.".".$key. " = ".db_format($value, $field->data_type);
						}
					}
				}
			}
			
			$sql_where = "";
			//IF THE MAIN TABLE IS THE TEAM-MEMBERS THEN 
			//JOIN WITH GROUP-MEMBERS TABLE AND SELECT THE SPECIFIC SUBSCRIPTION
			//if (property_exists($obj, "client") && $obj->sql_table != "clients"){
			//	$sql_where = " WHERE $main_table.client = '".$obj->client."'"; 
			//}
			
			if ($sql_single != ""){
				if ($sql_where != "") 
					$sql_where .= " AND ";
				else
					$sql_where = " WHERE ";
				$sql_where .= $sql_single;
			}
			
			//NOW BUILD THE WHERE CLAUSE
			if (property_exists($obj, "filter")){
				foreach($obj->filter as $filter){
					foreach($alias_list as $alias){
						if ($alias["table"] != $filter->sql_table) continue;
						if ($sql_where != "") 
							$sql_where .= " AND ";
						else
							$sql_where .= " WHERE ";
						
						if (is_array($filter->value)){
							if (property_exists($filter, "data_type") && $filter->data_type == "date"){
								if (count($filter->value) != 2){
									exit_with_response(-1, "For data-type [date] you must specify an array of two elements");
								}
								
								$sql_where .= "CAST(".$alias["alias"].".".$filter->field. " AS DATE) BETWEEN '".$filter->value[0]."' AND '".$filter->value[1]."'";
							} else {
								$value = "";
								foreach($filter->value as $v){
									$v = trim($v);
									if ($v != ""){
										$v = format_db_field($v);
										if ($value != "") $value .= ",";
										$value .= "'".$v."'";
									}
								}
								if ($value != "")
									$sql_where .= $alias["alias"].".".$filter->field. " IN ($v)";
							}
						} else {
							if ($filter->value == null){
								$sql_where .= $alias["alias"].".".$filter->field. " IS NULL ";
							} else {
								$sql_where .= $alias["alias"].".".$filter->field. " = ".db_format($filter->value);
							}
						}
					}
				}
			}
			
			$sql = "SELECT $sql_fields FROM ".$obj->sql_table." $main_table $sql_tables $sql_where";
		}
		else{
			$sql_where = "";
			$sql_tables = "";
			
			//if (property_exists($obj, "client") && $obj->sql_table != "clients"){
			//	$sql_where = " WHERE ".$obj->sql_table.".client = '".$obj->client."'"; 
			//}

			$sql = "SELECT ".$obj->sql_table.".* FROM ".$obj->sql_table.$sql_tables.$sql_where;
		}
		
		return $sql;
	}
	
	
	function sql_query_to_json($query){
		$rst    = new CDatabase();
		return json_encode($rst->run_query($query));
	}
	
	function db_format($value, $format = "general"){
		if ($value == null) return "NULL";
		
		switch($format){
			case "number":
				if (trim($value) == "" || !is_numeric(trim($value)))
					return "NULL";
				break;
			case "date":
				if (!is_numeric($value)){
					if (strtotime($value) === false)
						return "NULL";
					
					$value = strtotime($value);
				}
					
				$value = date("Y-m-d", $value);
				break;
			case "time":
				 $value = date("H:i:s", $value);
				 break;
			case "datetime":
				if (!is_numeric($value)){
					if (strtotime($value) === false)
						return "NULL";
					
					$value = strtotime($value);
				}

				$value = date("Y-m-d H:i:s", $value);
				break;
		}
		
		$value = str_replace("'", "\'", $value);
		
		return "'".$value."'";
	}

?>
